import pymysql
import bcrypt
import random

conn = pymysql.connect(host='localhost', user='root', password='123456', database='rabc_db', charset='utf8')
cursor = conn.cursor()

# 角色
def insert_roles():
    roles = ['学生', '教师', '管理员']
    for r in roles:
        cursor.execute("INSERT IGNORE INTO role (name, description) VALUES (%s, %s)", (r, f"{r}角色"))

# 权限
def insert_permissions():
    permissions = ['查看成绩', '录入成绩', '管理用户']
    for p in permissions:
        cursor.execute("INSERT IGNORE INTO permission (name, description) VALUES (%s, %s)", (p, f"{p}权限"))

# 角色-权限
def insert_role_permissions():
    role_perm_map = {
        '学生': ['查看成绩'],
        '教师': ['查看成绩', '录入成绩'],
        '管理员': ['管理用户', '查看成绩', '录入成绩']
    }
    cursor.execute("SELECT id, name FROM role")
    role_id_map = {name: rid for rid, name in cursor.fetchall()}
    cursor.execute("SELECT id, name FROM permission")
    perm_id_map = {name: pid for pid, name in cursor.fetchall()}
    for role, perms in role_perm_map.items():
        for perm in perms:
            cursor.execute("INSERT IGNORE INTO role_permission (role_id, permission_id) VALUES (%s, %s)", (role_id_map[role], perm_id_map[perm]))
    return role_id_map, perm_id_map

# 用户
def insert_users(role_id_map):
    depts = ['计算机系', '数学系', '物理系']
    grades = ['2021', '2022', '2023']
    for i in range(1, 21):
        username = f'user{i}'
        real_name = f'用户{i}'
        user_type = random.choice(['学生', '教师', '管理员'])
        student_no = f'ST{i:04d}' if user_type == '学生' else None
        staff_no = f'SF{i:04d}' if user_type != '学生' else None
        dept = random.choice(depts)
        grade = random.choice(grades) if user_type == '学生' else None
        password = '123456'
        salt = bcrypt.gensalt()
        password_hash = bcrypt.hashpw(password.encode('utf-8'), salt)
        cursor.execute(
            "INSERT INTO user (username, password_hash, salt, real_name, user_type, student_no, staff_no, dept, grade) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (username, password_hash.decode('utf-8'), salt.decode('utf-8'), real_name, user_type, student_no, staff_no, dept, grade)
        )
        user_id = cursor.lastrowid
        # 分配角色
        cursor.execute("INSERT INTO user_role (user_id, role_id) VALUES (%s, %s)", (user_id, role_id_map[user_type]))

# 属性策略
def insert_attribute_policies(role_id_map, perm_id_map):
    depts = ['计算机系', '数学系', '物理系']
    # 只有“教师”且“dept=计算机系”才能“录入成绩”
    for dept in depts:
        cursor.execute(
            "INSERT INTO attribute_policy (role_id, permission_id, attribute_key, attribute_value) VALUES (%s, %s, %s, %s)",
            (role_id_map['教师'], perm_id_map['录入成绩'], 'dept', dept)
        )

if __name__ == '__main__':
    insert_roles()
    insert_permissions()
    role_id_map, perm_id_map = insert_role_permissions()
    insert_users(role_id_map)
    insert_attribute_policies(role_id_map, perm_id_map)
    conn.commit()
    cursor.close()
    conn.close()
    print("测试数据已生成。") 